﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_timecard')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_timecard

GO

-- =============================================
-- Author:      YFC
-- Create date: 2019-12-25 14:18:25
-- Database:    scm_main
-- Description: 考勤管理-考勤卡计算
-- =============================================
CREATE PROCEDURE bd_proc_timecard
  @date DATE
 ,@users VARCHAR(MAX) = ''
 ,@flag INT = 0   --0:自动计算  1:补出勤  2:请假 3:加班 4:排班, 5:加班审批（通过、驳回） 6:请假审批（通过、驳回）  7：取消假期
AS
BEGIN
  SET NOCOUNT ON;


  CREATE TABLE #timecard
  (
    user_serial BIGINT NOT NULL
    , user_no VARCHAR(20) NOT NULL
    , user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , dept_id BIGINT NOT NULL
    , dept_name VARCHAR(500) COLLATE Chinese_PRC_CI_AS
    , card_date DATE NOT NULL
    , card_week VARCHAR(20) COLLATE Chinese_PRC_CI_AS

    , shift_id INT NOT NULL DEFAULT 0
    , shift_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , shift_begin INT NOT NULL DEFAULT 0  --上班时间(排班开始时间)
    , shift_end INT NOT NULL DEFAULT 0    --下班时间(排班结束时间)
    , shift_sign_begin INT DEFAULT 0   --开始签到时间
    , shift_sign_end INT DEFAULT 0     --结束签退时间
    , shift_late INT DEFAULT 0       --迟到结束时间
    , shift_early INT DEFAULT 0      --早退开始时间
    , shift_duty_on_delay INT DEFAULT 0   --上班后X分钟内不计迟到
    , shift_duty_off_delay INT DEFAULT 0   --下班后X分钟内不计早退
    , shift_workday DECIMAL(8, 2) DEFAULT 0         --本时段应记为X工作日
    , shift_workday_hours INT DEFAULT 0   --等于X分钟
    --    , shift_is_eight_three INT DEFAULT 0   --是否8+3班次
    , shift_is_flextime INT DEFAULT 0       --是否弹性班次
    , shift_flextime_interval_begin INT DEFAULT 0   -- 上班时间弹性间隔
    , shift_flextime_interval_end INT DEFAULT 0   -- 下班时间弹性间隔

    , shift_actual_begin INT DEFAULT 0   -- 实际上班时间
    , shift_actual_end INT DEFAULT 0   -- 实际下班时间

    , shift_late_time DATETIME    --迟到结束日期时间
    , shift_early_time DATETIME  --早退开始日期时间
    , shift_duty_begin DATETIME  --上班打卡日期时间（包括不计迟到的分钟数）,在这之前的打卡算正常
    , shift_duty_end DATETIME    --下班打卡日期时间（包括不计早退的分钟数）,在这之后的打卡算正常
    , shift_card_begin DATETIME   --打卡签到开始日期时间
    , shift_card_end DATETIME     --打卡签退日期时间

    , swipe_begin_actual INT DEFAULT 0   -- 实际上班时间
    , swipe_end_actual INT DEFAULT 0     -- 实际下班时间

    , swipe_begin INT NOT NULL DEFAULT 0    -- 打卡开始时间
    , swipe_end INT NOT NULL DEFAULT 0      -- 打卡结束时间

    , swipe_first INT DEFAULT 0 -- 第一次打卡时间
    , swipe_last INT DEFAULT 0   -- 最后一次打卡时间

    , should_hours INT NOT NULL DEFAULT 0   -- 应出工时
    , actual_hours INT NOT NULL DEFAULT 0   -- 正常工时
    , total_hours INT NOT NULL DEFAULT 0    --总工时=正常工时+加班工时+请假时长
    , status INT NOT NULL DEFAULT -1

    , shift_middle_count INT NOT NULL DEFAULT 0   --中班次数
    , shift_night_count INT NOT NULL DEFAULT 0    --夜班次数

    , is_enjoyment_subsidized INT DEFAULT 0       --是否中夜班
    , middle_shift_start_time INT DEFAULT 0       --中班开始时间
    , middle_shift_end_time INT DEFAULT 0         --中班结束时间
    , night_shift_start_time INT DEFAULT 0        --夜班开始时间
    , night_shift_end_time INT DEFAULT 0          --夜班结束时间

    , repast_start_time INT DEFAULT 0     --就餐1开始时间
    , repast_end_time INT DEFAULT 0       --就餐1结束时间
    , repast_first INT DEFAULT 0         --是否就餐第一次
    , repast_is_83_normal INT DEFAULT 0   --是否8+3的正常上班就餐，如果就餐时间在加班的时间内，=1

    , repast_start_time2 INT DEFAULT 0    --就餐2开始时间
    , repast_end_time2 INT DEFAULT 0      --就餐2结束时间
    , repast_second INT DEFAULT 0         --是否就餐第二次
    , repast_is_83_normal2 INT DEFAULT 0  --是否8+3的正常上班就餐，如果就餐时间在加班的时间内，=1

    , repast_hours INT DEFAULT 0   --需要扣除的就餐时长，根据上下班打卡时间，判断是否包含就餐时间，计算就餐几次
    , repast_count INT DEFAULT 0   -- 就餐次数

    , pending_hours INT NOT NULL DEFAULT 0    -- 待处理时间，小于2小时不显示

    , ot_bill_id INT DEFAULT 0         --加班单ID
    , ot_bill_check INT DEFAULT 0       --加班状审批状态
    , ot_bill_status INT DEFAULT 0     --加班单状态 0：不能操作  1：能操作
    , ot_shift_id INT DEFAULT 0         --加班班次ID
    , ot_type_id INT NULL DEFAULT 0
    , ot_type_83 INT DEFAULT 0          --8+3班次类型，0：8+3   1：3+8
    , ot_type_name VARCHAR(50) COLLATE Chinese_PRC_CI_AS
    , ot_begin INT NOT NULL DEFAULT 0   -- 加班申请开始时间
    , ot_end INT NOT NULL DEFAULT 0     -- 加班申请结束时间
    , ot_hours INT NOT NULL DEFAULT 0
    , ot_is_eating INT NOT NULL DEFAULT 0
    , ot_pending_hours INT NOT NULL DEFAULT 0   --加班待确认时长
    , ot_card_begin INT DEFAULT 0       --加班打卡开始时间
    , ot_card_end INT DEFAULT 0         --加班打卡结束时间
    --   ,ot_begin_time DATETIME            --加班开始日期时间
    --   ,ot_end_time DATETIME              --加班结束日期时间
    , ot_min INT DEFAULT 0              --加班最低时长
    , ot_min_increment INT DEFAULT 0    --加班最低增量分钟

    , ot_normal_hours INT DEFAULT 0       --平时加班时长
    , ot_weekend_hours INT DEFAULT 0      --周末加班时长
    , ot_holiday_hours INT DEFAULT 0      --法定节假日加班时长
    , ot_to_rest_hours INT DEFAULT 0      --加班转补休时长
    , ot_pending_hours_confirm INT DEFAULT 0   -- 加班确认总时长=平时加班时长+周末加班时长+法定节假日加班时长+加班转补休时长

    , ot_shift_sign_begin INT DEFAULT 0     --加班开始签到时间
    , ot_shift_sign_end INT DEFAULT 0       --加班结束签退时间
    , ot_shift_card_begin DATETIME   --加班打卡签到开始日期时间
    , ot_shift_card_end DATETIME     --加班打卡签退日期时间
    , ot_repast_start_time INT DEFAULT 0     --加班就餐1开始时间
    , ot_repast_end_time INT DEFAULT 0       --加班就餐1结束时间
    , ot_repast_start_time2 INT DEFAULT 0    --加班就餐2开始时间
    , ot_repast_end_time2 INT DEFAULT 0      --加班就餐2结束时间

    , ot_repast_hours INT DEFAULT 0   --需要扣除的就餐时长，根据上下班打卡时间，判断是否包含就餐时间，计算就餐几次

    , leave_hours_total INT DEFAULT 0   --请假总时长
    , leave_type VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , leave_hours VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , leave_bill_id INT DEFAULT 0   -- 请假单ID

    , user_card_type INT     --员工考勤打卡类型

    --    , should_repast_count INT DEFAULT 0     --应就餐次数
    --    , can_repast INT DEFAULT 0   --是否可以就餐
    , is_swipe INT DEFAULT 0   -- 是否有打卡记录
    --    , is_swipe_correct  INT DEFAULT 0   -- 打卡是否正常


    , sign_begin_date DATETIME    -- 打卡记录开始日期时间
    , sign_end_date DATETIME      -- 打卡记录结束日期时间

    , sign_begin INT DEFAULT 0   -- 打卡记录开始时间
    , sign_end INT DEFAULT 0   -- 打卡记录结束时间

    , swipe_begin_repair INT DEFAULT 0   -- 上班打卡时间是补出勤
    , swipe_end_repair INT DEFAULT 0     -- 下班打卡时间是补出勤

    , flag INT DEFAULT 0   -- 操作标识
  )

  -- 打卡记录临时表
  CREATE TABLE #cardData
  (
    user_serial BIGINT
    , sj DATETIME
    , mode INT
    , swipe_time INT DEFAULT 0
    , bh INT
    , prevBh INT
    , prevSj DATETIME
    , date_diff INT
    , is_valid INT DEFAULT 1
    , row_index INT
    , whether_validity INT
  )


  -- 打卡记录临时，用于更新表kt_jl状态
  CREATE TABLE #cardSave
  (
    bh INT
    , is_valid INT
  )

  IF (ISNULL(@users, '') = '')
  BEGIN
    INSERT INTO #timecard(user_serial, user_no, user_name, dept_id, dept_name, card_date, card_week, user_card_type, flag)
    SELECT user_serial, user_no, user_name, dept_serial, dept_name, @date,
     CASE
                                                                     DATENAME(dw, @date)
       WHEN 'Monday' THEN
         '星期一'
       WHEN 'Tuesday' THEN
         '星期二'
       WHEN 'Wednesday' THEN
         '星期三'
       WHEN 'Thursday' THEN
         '星期四'
       WHEN 'Friday' THEN
         '星期五'
       WHEN 'Saturday' THEN
         '星期六'
       WHEN 'Sunday' THEN
         '星期日'
       ELSE
         DATENAME(dw, @date)
     END
     , kqdklx, @flag
    FROM bd_view_user


    -- 更新加班确认时长
    UPDATE card
    SET ot_normal_hours = kq.ot_normal_hours
       ,ot_weekend_hours = kq.ot_weekend_hours
       ,ot_holiday_hours = kq.ot_holiday_hours
       ,ot_to_rest_hours = kq.ot_to_rest_hours
       ,flag = op_flag
    FROM #timecard card
    INNER JOIN (
        SELECT
          user_serial, ot_normal_hours, ot_weekend_hours, ot_holiday_hours, ot_to_rest_hours, op_flag
        FROM bd_kq_timecard
        WHERE card_date = @date
      ) kq ON kq.user_serial = card.user_serial

  END
  ELSE
  BEGIN
    INSERT INTO #timecard(user_serial, user_no, user_name, dept_id, dept_name, card_date, card_week, user_card_type, flag)
    SELECT user_serial, user_no, user_name, dept_serial, dept_name, @date,
     CASE
                                                                     DATENAME(dw, @date)
       WHEN 'Monday' THEN
         '星期一'
       WHEN 'Tuesday' THEN
         '星期二'
       WHEN 'Wednesday' THEN
         '星期三'
       WHEN 'Thursday' THEN
         '星期四'
       WHEN 'Friday' THEN
         '星期五'
       WHEN 'Saturday' THEN
         '星期六'
       WHEN 'Sunday' THEN
         '星期日'
       ELSE
         DATENAME(dw, @date)
     END, kqdklx, @flag
    FROM STRING_SPLIT(@users, ',') u
    INNER JOIN bd_view_user u2 ON u2.user_serial = u.value

    -- 更新加班确认时长
    UPDATE card
    SET ot_normal_hours = kq.ot_normal_hours
       ,ot_weekend_hours = kq.ot_weekend_hours
       ,ot_holiday_hours = kq.ot_holiday_hours
       ,ot_to_rest_hours = kq.ot_to_rest_hours
       ,flag = op_flag
    FROM #timecard card
    INNER JOIN (
        SELECT
          user_serial, ot_normal_hours, ot_weekend_hours, ot_holiday_hours, ot_to_rest_hours, op_flag
        FROM STRING_SPLIT(@users, ',') u
      INNER JOIN (
        SELECT
          user_serial, ot_normal_hours, ot_weekend_hours, ot_holiday_hours, ot_to_rest_hours, op_flag
        FROM bd_kq_timecard
        WHERE card_date = @date
      ) u2 ON u2.user_serial = u.value
      ) kq ON kq.user_serial = card.user_serial
  END

  -- TODO 更新临时部门调整的部门ID


  -- 中夜班时间
  UPDATE #timecard
  SET middle_shift_start_time = 19 * 60
     ,middle_shift_end_time = 22 * 60
     ,night_shift_start_time = 24 * 60
     ,night_shift_end_time = 28 * 60

  --排班
  UPDATE card
  SET shift_id = sch.shift_id
     ,shift_name = bc.mc
     ,shift_begin = sd.sbsj
     ,shift_end = sd.xbsj
     ,shift_sign_begin = sd.sbks
     ,shift_sign_end = sd.xbjs
     ,shift_late = sd.cdsj
     ,shift_early = sd.ztsj
     ,shift_duty_on_delay = sd.bjcd
     ,shift_duty_off_delay = sd.bjzt
     ,shift_workday = sd.alld
     ,shift_workday_hours = sd.allt

     ,shift_is_flextime = sd.is_flextime
     ,shift_flextime_interval_begin = sd.flextime_interval_begin
     ,shift_flextime_interval_end = sd.flextime_interval_end

     ,repast_start_time = ISNULL(sd.repast_start_time, 0)
     ,repast_end_time = ISNULL(sd.repast_end_time, 0)
     ,repast_start_time2 = ISNULL(sd.repast_start_time2, 0)
     ,repast_end_time2 = ISNULL(sd.repast_end_time2, 0)

  FROM #timecard card
  INNER JOIN bd_kq_schedule_detail sch ON sch.user_serial = card.user_serial
        AND sch.shift_date = card.card_date
  INNER JOIN zt_banci bc ON bc.id = sch.shift_id
  INNER JOIN zt_shid sd ON sd.parent = bc.bh
        AND sd.lx = 0

  -- 8+3班次加班信息
  UPDATE card
  SET ot_begin = sd.sbsj
     ,ot_end = sd.xbsj
     ,ot_hours = ISNULL(sd.xbsj, 0) - ISNULL(sd.sbsj, 0)
     ,ot_bill_status = 1
     ,ot_bill_id = -1
     ,ot_type_id = 1
      -- 8+3: 加班开始时间>=排班班次结束时间   1
      -- 3+8：加班结束时间<=排班班次结束时间   2
     ,ot_type_83 =
        CASE
          WHEN sd.sbsj >= card.shift_end THEN
            1
          WHEN sd.xbsj <= card.shift_end THEN
            2
          ELSE
            0
        END
  --     ,ot_type_name = '8+3加班'
  FROM #timecard card
  INNER JOIN zt_banci bc ON bc.id = card.shift_id
  INNER JOIN zt_shid sd ON sd.parent = bc.bh AND sd.lx = 1

  UPDATE #timecard
  SET ot_type_name =
    CASE
                                                                    ot_type_83
      WHEN 1 THEN
        '8+3'
      WHEN 2 THEN
        '3+8'
      ELSE
        ''
    END


  UPDATE #timecard
  SET shift_card_begin =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(ISNULL(shift_sign_begin, 0), card_date)
          ELSE
            NULL
        END
     ,shift_card_end =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(IIF(ISNULL(shift_sign_end, 0) = 0, 1439, shift_sign_end), card_date)
          ELSE
            NULL
        END
     ,shift_duty_begin =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(ISNULL(shift_begin, 0) + ISNULL(shift_duty_on_delay, 0), card_date)
          ELSE
            NULL
        END
     ,shift_duty_end =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(ISNULL(shift_end, 0) - ISNULL(shift_duty_off_delay, 0), card_date)
          ELSE
            NULL
        END
     ,shift_late_time =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(ISNULL(shift_late, 0), card_date)
          ELSE
            NULL
        END
     ,shift_early_time =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            dbo.bd_fn_minToDateTime(ISNULL(shift_early, 0), card_date)
          ELSE
            NULL
        END
  --     ,ot_begin_time = dbo.bd_fn_minToDateTime(ISNULL(ot_begin, 0), card_date)
  --     ,ot_end_time = dbo.bd_fn_minToDateTime(ISNULL(ot_end, 0), card_date)


  --==================================================加班信息==================================================
  --8+3，1.5小时起，30分钟增量
  --其他加班，2小时起，30分钟增量 
  UPDATE #timecard
  SET ot_min =
        CASE
          WHEN ot_type_83 IN (1, 2) THEN
            90
          ELSE
            120
        END
     ,ot_min_increment =
        CASE
          WHEN ot_type_83 IN (1, 2) THEN
            30
          ELSE
            30
        END

  -- ====================================================加班申请信息====================================================
  -- 没排班的加班信息
  UPDATE card
  SET ot_type_id = ot.over_time_type_id
     ,ot_type_name = im.name --CASE WHEN card.ot_type_83 IN(1,2) THEN ot_type_name + '/' + im.name ELSE im.name END
     ,ot_begin = ot.ot_begin
     ,ot_end = ot.ot_end
     ,ot_hours = ot.jsjg
     ,ot_is_eating = ot.is_repast
     ,ot_bill_id = ot.order_id
     ,ot_shift_id = ot.shift_id
     ,ot_bill_check = otorder.status
     ,ot_bill_status =
        CASE
          WHEN otorder.status = 3 OR otorder.status = 4 THEN
            1
          ELSE
            0
        END
  FROM #timecard card
  INNER JOIN (
      SELECT
        *
      FROM lr_jiaba
      WHERE jbrq = @date
        AND ISNULL(is_delete, 0) = 0
    ) ot ON ot.user_serial = card.user_serial
        AND ot.jbrq = card.card_date
  INNER JOIN bd_over_time_order otorder ON otorder.order_id = ot.order_id AND ISNULL(otorder.is_delete, 0) = 0
  INNER JOIN bd_sys_item im ON im.id = ot.over_time_type_id
  WHERE otorder.status <> 2
  AND card.shift_id = 0

  -- 有排班的加班信息，加班开始时间要>=排班下班结束时间
  UPDATE card
  SET ot_type_id = ot.over_time_type_id
     ,ot_type_name = im.name --CASE WHEN card.ot_type_83 IN(1,2) THEN ot_type_name + '/' + im.name ELSE im.name END
     ,ot_begin =
        CASE
          WHEN card.ot_type_83 IN (1, 2) THEN
            card.ot_begin
          ELSE
            ot.ot_begin
        END
     ,ot_end = ot.ot_end
     ,ot_hours =
        CASE
          WHEN card.ot_type_83 IN (1, 2) THEN
            card.ot_hours + ot.jsjg
          ELSE
            ot.jsjg
        END
     ,ot_is_eating = ot.is_repast
     ,ot_bill_id = ot.order_id
     ,ot_shift_id = ot.shift_id
     ,ot_bill_check = otorder.status
     ,ot_bill_status =
        CASE
          WHEN otorder.status = 3 OR otorder.status = 4 THEN
            1
          ELSE
            0
        END
  FROM #timecard card
  INNER JOIN (
      SELECT
        *
      FROM lr_jiaba
      WHERE jbrq = @date
        AND ISNULL(is_delete, 0) = 0
    ) ot ON ot.user_serial = card.user_serial
        AND ot.jbrq = card.card_date
  INNER JOIN bd_over_time_order otorder ON otorder.order_id = ot.order_id AND ISNULL(otorder.is_delete, 0) = 0
  INNER JOIN bd_sys_item im ON im.id = ot.over_time_type_id
  WHERE otorder.status <> 2
  AND card.shift_id > 0
  -- 有排班的加班，非8+3，验证加班申请记录的加班结束时间是否>=排班的下班时间+2小时
  -- 有排班的加班，8+3，验证加班申请记录的加班结束时间是否>=8+3的加班结束时间+1.5小时
  AND ot.ot_end >= IIF(card.ot_type_83 IN (1, 2), card.ot_end + card.ot_min, card.shift_end + card.ot_min)



  -- 只有加班，没排班，获取班次的签到签退时间
  UPDATE card
  SET shift_sign_begin = sd.sbks
     ,shift_sign_end = sd.xbjs
     ,ot_shift_card_begin = dbo.bd_fn_minToDateTime(ISNULL(sd.sbks, 0), card_date)
     ,ot_shift_card_end = dbo.bd_fn_minToDateTime(IIF(ISNULL(sd.xbjs, 0) = 0, 1439, sd.xbjs), card_date)

  --     ,repast_start_time = ISNULL(sd.repast_start_time, 0)
  --     ,repast_end_time = ISNULL(sd.repast_end_time, 0)
  --     ,repast_start_time2 = ISNULL(sd.repast_start_time2, 0)
  --     ,repast_end_time2 = ISNULL(sd.repast_end_time2, 0)
  FROM #timecard card
  INNER JOIN zt_banci bc ON bc.id = card.ot_shift_id
  INNER JOIN zt_shid sd ON sd.parent = bc.bh
        AND sd.lx = 0
  WHERE card.ot_bill_id > 0 AND card.shift_id = 0



  --1	1000001	38	平时加班
  --2	1000002	38	周末
  --3	1000003	38	法定节假日加班
  --4	1000004	38	加班转调休


  -- 打卡记录范围，前一天的最后有效打卡记录，后一天排班上班之前的打卡记录
  --  UPDATE card SET sign_begin = x.swipe_end, sign_begin_date = DATEADD(DAY, -1, @date)
  --  FROM #timecard card 
  --  INNER JOIN(
  --    SELECT user_serial, swipe_end FROM bd_kq_timecard WHERE card_date = DATEADD(DAY, -1, @date)
  --  ) x ON x.user_serial = card.user_serial


  DECLARE @cardBegin VARCHAR(50)
         ,@cardEnd VARCHAR(50)

  -- 没排班，有加班的时候，时间按加班的开始和结束计算
  UPDATE #timecard
  SET shift_card_begin =
        CASE
          WHEN ISNULL(shift_id, 0) = 0 AND ISNULL(ot_bill_id, 0) > 0 THEN
            ot_shift_card_begin
          ELSE
            shift_card_begin
        END
     ,shift_card_end =
        CASE
          WHEN ISNULL(shift_id, 0) = 0 AND ISNULL(ot_bill_id, 0) > 0 THEN
            ot_shift_card_end
          ELSE
            shift_card_end
        END
     ,shift_duty_begin =
        CASE
          WHEN ISNULL(shift_id, 0) = 0 AND ISNULL(ot_bill_id, 0) > 0 THEN
            ot_shift_card_begin
          ELSE
            shift_duty_begin
        END
     ,shift_duty_end =
        CASE
          WHEN ISNULL(shift_id, 0) = 0 AND ISNULL(ot_bill_id, 0) > 0 THEN
            ot_shift_card_end
          ELSE
            shift_duty_end
        END

  -- 部门总监的打卡时间取0-24小时
  UPDATE #timecard
  SET shift_card_begin =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 OR ISNULL(ot_bill_id, 0) > 0 THEN
            @date
          ELSE
            shift_card_begin
        END
     ,shift_card_end =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 OR ISNULL(ot_bill_id, 0) > 0 THEN
            CONVERT(VARCHAR(10), @date, 120) + ' 23:59:59'
          ELSE
            shift_card_end
        END
  WHERE user_card_type = 22

  -- 取最小时间和最大时间
  SELECT @cardBegin = CONVERT(VARCHAR(50), MIN(date), 120)
   , @cardEnd = CONVERT(VARCHAR(50), MAX(date), 120)
  FROM (
    SELECT
      shift_card_begin date
    FROM #timecard
    WHERE shift_card_begin IS NOT NULL
    UNION
    SELECT
      shift_card_end date
    FROM #timecard
    WHERE shift_card_end IS NOT NULL
    UNION
    SELECT
      ot_shift_card_begin date
    FROM #timecard
    WHERE ot_shift_card_begin IS NOT NULL
    UNION
    SELECT
      ot_shift_card_end date
    FROM #timecard
    WHERE ot_shift_card_end IS NOT NULL
  ) x

  IF (ISNULL(@cardBegin, '') = ''
    OR
    ISNULL(@cardEnd, '') = '')
  BEGIN
    SET @cardBegin = CONVERT(VARCHAR(10), @date, 120)
    SET @cardEnd = CONVERT(VARCHAR(10), @date, 120) + ' 23:59:59'
  END



  --==============================================================打卡记录开始=======================================================

  --取最小和最大时间范围内的打卡记录
  INSERT INTO #cardData(user_serial, sj, mode, swipe_time, bh, prevBh, prevSj, date_diff, whether_validity)
  SELECT card.user_serial
   , sj
   , jl.mode
   , dbo.bd_fn_datetimeToInt(@date, sj)
   , jl.bh
   , LAG(bh, 1, 0) OVER (PARTITION BY card.user_serial ORDER BY sj ASC)
   , LAG(sj, 1, 0) OVER (PARTITION BY card.user_serial ORDER BY sj ASC)
   , 0
   , jl.whether_validity
  FROM #timecard card
  INNER JOIN kt_jl jl ON jl.user_serial = card.user_serial
  WHERE sj >= @cardBegin
    AND sj <= @cardEnd
    AND whether_validity IN (0, 1, 3, 4)

  UPDATE #cardData
  SET date_diff = DATEDIFF(MINUTE, prevSj, sj)


  --  0未处理
  --  1手动有效
  --  2手动无效 
  --  3系统有效
  --  4系统无效

  -- 5分钟内连续打卡无效, 5分钟可以配置,补出勤的不修改状态
  UPDATE #cardData
  SET is_valid = 0
  WHERE date_diff <= 5

  UPDATE card
  SET row_index = x.RowNum
  FROM #cardData card
  INNER JOIN (
      SELECT
        *, RowNum = ROW_NUMBER() OVER (PARTITION BY user_serial ORDER BY sj)
      FROM #cardData
      WHERE is_valid = 1
    ) x ON x.bh = card.bh

  -- 奇偶算法， 只取最前面的2条数据作为有效打卡，其他全部无效，手动补出勤的状态不变
  UPDATE #cardData
  SET is_valid = 0
  WHERE row_index > 2

  UPDATE #cardData
  SET is_valid = 1
  WHERE row_index <= 2

  UPDATE #cardData
  SET whether_validity =
    CASE
      WHEN ISNULL(is_valid, 0) = 0 THEN
        4
      ELSE
        3
    END



  -- 把打卡记录状态保存在临时表中，最后更新物理表kt_jl
  INSERT INTO #cardSave(bh, is_valid)
  SELECT bh, ISNULL(whether_validity, 0) FROM #cardData

  -- 删除无效数据，用于计算考勤
  DELETE FROM #cardData
  WHERE is_valid = 0
  --

  --==============================================================打卡记录结束=======================================================


  -- 每个人的打卡记录开始时间和结束时间
  UPDATE #timecard
  SET sign_begin_date = x.minDate
     ,sign_end_date = x.maxDate
  FROM #timecard card
  INNER JOIN (
      SELECT
        x.user_serial, MIN(date) minDate, MAX(date) maxDate
      FROM (
        SELECT
          user_serial, shift_card_begin date
        FROM #timecard
        WHERE shift_card_begin IS NOT NULL
        UNION
        SELECT
          user_serial, shift_card_end date
        FROM #timecard
        WHERE shift_card_end IS NOT NULL
        UNION
        SELECT
          user_serial, ot_shift_card_begin date
        FROM #timecard
        WHERE ot_shift_card_begin IS NOT NULL
        UNION
        SELECT
          user_serial, ot_shift_card_end date
        FROM #timecard
        WHERE ot_shift_card_end IS NOT NULL
      ) x
      GROUP BY x.user_serial
    ) x ON x.user_serial = card.user_serial



  UPDATE #timecard
  SET shift_card_begin = ISNULL(shift_card_begin, @cardBegin)
     ,shift_card_end = ISNULL(shift_card_end, @cardEnd)
     ,sign_begin_date = ISNULL(sign_begin_date, @cardBegin)
     ,sign_end_date = ISNULL(sign_end_date, @cardEnd)



  -- 实际上班下班时间
  -- 正常排班，上下班时间是排班时间
  UPDATE #timecard
  SET shift_actual_begin = shift_begin
     ,shift_actual_end = shift_end
  -- 8+3,下班时间是加班结束时间
  UPDATE #timecard
  SET shift_actual_end = ot_end
  WHERE ot_type_83 = 1
  -- 3+8,上班时间是加班开始时间
  UPDATE #timecard
  SET shift_actual_begin = ot_begin
  WHERE ot_type_83 = 2

  -- 正常加班申请
  --  UPDATE #timecard
  --  SET shift_card_begin =
  --        CASE
  --          WHEN ot_begin < shift_begin THEN
  --            dbo.bd_fn_minToDateTime(ot_begin, @date)
  --          ELSE
  --            shift_card_begin
  --        END
  --     ,shift_card_end =
  --        CASE
  --          WHEN ot_end > shift_end THEN
  --            dbo.bd_fn_minToDateTime(ot_end, @date)
  --          ELSE
  --            shift_card_end
  --        END
  --  WHERE ot_type_83 = 0


  --获取第一次打卡和最后一次打卡
  UPDATE card
  SET swipe_first = dbo.bd_fn_datetimeToInt(@date, x.first_swipe)
     ,swipe_last = dbo.bd_fn_datetimeToInt(@date, x.last_swipe)
  FROM #timecard card
  INNER JOIN (
      SELECT
        card.user_serial, MIN(sj) first_swipe, MAX(sj) last_swipe
      FROM #timecard card
    INNER JOIN #cardData kt ON kt.user_serial = card.user_serial
      WHERE sj >= card.shift_card_begin
        AND sj <= card.shift_card_end
      GROUP BY card.user_serial
    ) x ON x.user_serial = card.user_serial
  WHERE x.last_swipe <= @cardEnd

  -- 8+3的班次上下班打卡取第一次和最后一次
  UPDATE #timecard
  SET swipe_begin = swipe_first
     ,swipe_end = swipe_last
  WHERE ot_type_83 > 0


  --上班时间后一次打卡（迟到打卡）
  UPDATE card
  SET swipe_begin = dbo.bd_fn_datetimeToInt(@date, x.swipe_begin)
  FROM #timecard card
  INNER JOIN (
      SELECT
        card.user_serial, MAX(sj) swipe_begin
      FROM #timecard card
    INNER JOIN #cardData kt ON kt.user_serial = card.user_serial
      WHERE kt.sj >= card.shift_card_begin
        AND sj <= card.shift_late_time
        AND ISNULL(card.shift_id, 0) > 0
        -- 普通班次和8+3班次计算迟到
        AND ot_type_83 IN (0, 1)
      GROUP BY card.user_serial
    ) x ON card.user_serial = x.user_serial



  --上班时间前一次打卡（正常打卡）
  UPDATE card
  SET swipe_begin = dbo.bd_fn_datetimeToInt(@date, x.swipe_begin)
  FROM #timecard card
  INNER JOIN (
      SELECT
        card.user_serial, MAX(sj) swipe_begin
      FROM #timecard card
    INNER JOIN #cardData kt ON kt.user_serial = card.user_serial
      WHERE kt.sj >= card.shift_card_begin
        AND sj <= card.shift_duty_begin
        AND ISNULL(card.shift_id, 0) > 0
        AND ot_type_83 = 0
      GROUP BY card.user_serial
    ) x ON card.user_serial = x.user_serial



  --下班时间前一次打卡（早退打卡）
  UPDATE card
  SET swipe_end = dbo.bd_fn_datetimeToInt(@date, x.swipe_begin)
  FROM #timecard card
  INNER JOIN (
      SELECT
        card.user_serial, MAX(sj) swipe_begin
      FROM #timecard card
    INNER JOIN #cardData kt ON kt.user_serial = card.user_serial
      WHERE kt.sj >= card.shift_early_time
        AND sj <= card.shift_duty_end
        AND ISNULL(card.shift_id, 0) > 0
        -- 普通班次和3+8班次计算早退
        AND ot_type_83 IN (0, 2)
      GROUP BY card.user_serial
    ) x ON card.user_serial = x.user_serial

  --下班时间后一次打卡（正常打卡）
  UPDATE card
  SET swipe_end = dbo.bd_fn_datetimeToInt(@date, x.swipe_first)
  FROM #timecard card
  INNER JOIN (
      SELECT
        card.user_serial, MIN(sj) swipe_first, MAX(sj) swipe_last
      FROM #timecard card
    LEFT JOIN #cardData kt ON kt.user_serial = card.user_serial
      WHERE sj >= card.shift_duty_end
        AND sj <= card.shift_card_end
        AND ot_type_83 = 0
      GROUP BY card.user_serial
    ) x ON card.user_serial = x.user_serial


  --如果最后刷卡时间=第一次刷卡时间，记为第一次刷卡，最后刷卡置空
  UPDATE #timecard
  SET swipe_last =
    CASE
      WHEN ISNULL(swipe_last, 0) = ISNULL(swipe_first, 0) THEN
        0
      ELSE
        swipe_last
    END


  UPDATE #timecard
  SET swipe_begin =
        CASE
          WHEN ISNULL(swipe_begin, 0) = 0 THEN
            ISNULL(swipe_first, 0)
          ELSE
            ISNULL(swipe_begin, 0)
        END
     ,swipe_end =
        CASE
          WHEN ISNULL(swipe_last, 0) = 0 THEN
            0
          WHEN ISNULL(swipe_end, 0) = 0 AND ISNULL(swipe_last, 0) > ISNULL(swipe_begin, 0) THEN
            ISNULL(swipe_last, 0)
          WHEN ISNULL(swipe_end, 0) = ISNULL(swipe_begin, 0) THEN
            0
          ELSE
            ISNULL(swipe_end, 0)
        END

  --部门总监
  --上班时长大于8小时正常,一天不够8小时提示异常，扣除吃饭时间（半小时）
  --打卡时间取当天24小时内的最早打卡时间和最晚打卡时
  --user_card_type=22
  UPDATE #timecard
  SET swipe_begin = swipe_first
     ,swipe_end = swipe_last
  WHERE user_card_type = 22

  --弹性工作制
  --正常排班，按正常时间上班，可以比正常上班时间晚到X分钟，下班必须把晚到的时间补回来，否则提示异常，扣除吃饭时间（半小时），晚于x时间算缺勤，提示异常；
  --这些员工每日的有效考勤工时需>=8小时
  --user_card_type=21


  --中夜班次数			
  --1.      中班次数：19:00前打卡上班，22：00之后结束工作，记为1次			
  --2.      夜班次数：00:00前打卡上班，04：00之后结束工作，记为1次			

  UPDATE #timecard
  SET shift_middle_count =
        CASE
          WHEN (shift_id > 0 OR ot_bill_id > 0) AND swipe_begin > 0 THEN
            CASE
              WHEN swipe_first <= middle_shift_start_time AND
                swipe_last > middle_shift_end_time THEN
                1
              ELSE
                0
            END
          ELSE
            0
        END
     ,shift_night_count =
        CASE
          WHEN (shift_id > 0 OR ot_bill_id > 0) AND swipe_begin > 0 THEN
            CASE
              WHEN swipe_first <= night_shift_start_time AND
                swipe_last >= night_shift_end_time THEN
                1
              ELSE
                0
            END
          ELSE
            0
        END


  --加班打卡时间
  UPDATE card
  SET ot_card_begin = card.ot_begin
     ,ot_card_end = dbo.bd_fn_datetimeToInt(@date, x.last_swipe)
  FROM #timecard card
  INNER JOIN (
      SELECT
        card.user_serial, MIN(sj) first_swipe, MAX(sj) last_swipe
      FROM #timecard card
    INNER JOIN #cardData d ON d.user_serial = card.user_serial
      WHERE d.sj >= card.shift_card_begin
        AND d.sj <= card.shift_card_end
        -- 8+3班次不计算加班打卡记录
        AND ISNULL(card.ot_bill_id, 0) > 0
      GROUP BY card.user_serial
    ) x ON x.user_serial = card.user_serial



  --==================================================请假==================================================
  DECLARE @leave TABLE
  (
    user_serial INT
    , leave_type_id VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , leave_type_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , leave_hours INT
    , leave_status INT
    , leave_date DATETIME
    , leave_bill_id INT
  )

  --年假、调休需要审批
  INSERT INTO @leave(user_serial, leave_type_id, leave_hours, leave_status, leave_date, leave_bill_id)
  SELECT card.user_serial
   , leave_id
   , x.jsjg
   , x.status
   , leave_date
   , order_id
  FROM #timecard card
  INNER JOIN (
      SELECT
        leave.user_serial, lor.leave_id, leave.jsjg, lor.status, leave.kssj leave_date, lor.order_id
      FROM lr_kqingj leave
    INNER JOIN bd_leave_order lor ON lor.order_id = leave.order_id
      WHERE leave.kssj = @date AND lor.status <> 2
        AND ISNULL(leave.whether_validity, 0) = 1 AND ISNULL(lor.is_del, 0) = 0
    ) x ON x.user_serial = card.user_serial


  UPDATE lv
  SET leave_type_name = jl.mc
  FROM @leave lv
  INNER JOIN kt_qingj jl ON jl.bh = lv.leave_type_id

  --请假总时长
  UPDATE card
  SET leave_hours_total = x.total
  FROM #timecard card
  INNER JOIN (
      SELECT
        user_serial, SUM(leave_hours) total
      FROM @leave
      WHERE leave_status IN (3, 4)
      GROUP BY user_serial
    ) x ON x.user_serial = card.user_serial

  -- 更新请假单ID
  UPDATE card
  SET leave_bill_id = lv.leave_bill_id
  FROM #timecard card
  INNER JOIN (
      SELECT
        *
      FROM @leave
      WHERE leave_status IN (0, 1, 3)
    ) lv ON lv.user_serial = card.user_serial


  --请假假类名称
  UPDATE card
  SET leave_type = x.leave_type
     ,leave_hours = x.leave_hours
  FROM #timecard card
  INNER JOIN (
      SELECT
        b.user_serial, STUFF((SELECT '| ' + CAST(a.leave_type_name AS VARCHAR) FROM @leave a WHERE b.user_serial = a.user_serial AND leave_status IN (3, 4) FOR XML PATH ('')), 1, 1, '') leave_type, STUFF((SELECT '| ' + CAST(dbo.bd_fn_numToMin2(a.leave_hours) AS VARCHAR) FROM @leave a WHERE b.user_serial = a.user_serial AND leave_status IN (3, 4) FOR XML PATH ('')), 1, 1, '') leave_hours
      FROM @leave b
      WHERE leave_status IN (3, 4)
      GROUP BY b.user_serial
    ) x ON x.user_serial = card.user_serial

  UPDATE #timecard
  SET swipe_begin_actual =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 AND ISNULL(swipe_begin, 0) > 0 THEN
            CASE
              WHEN ot_type_83 = 1 THEN
                swipe_begin
              -- 弹性，打卡时间如果超过弹性上班结束时间(8:00-9:00弹性上班,打卡时间>9:00)，此时不再享受弹性加班，按正常上班时间计算
              WHEN user_card_type = 21 AND shift_is_flextime = 1 THEN
                 swipe_begin
--                IIF(swipe_begin > (shift_begin + shift_flextime_interval_begin), shift_begin, swipe_begin)
              -- 打卡时间>=签到开始时间,<排班上班时间+X分钟内不计迟到
              -- 正常打卡，实际上班时间=排班上班时间，否则=上班打卡时间
              WHEN swipe_begin >= shift_sign_begin AND swipe_begin <= shift_begin + shift_duty_on_delay THEN
                shift_begin
              ELSE
                swipe_begin
            END
          ELSE
            swipe_begin_actual
        END
     ,swipe_end_actual =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 AND ISNULL(swipe_end, 0) > 0 THEN
            CASE
              WHEN ot_type_83 = 2 THEN
                swipe_end
              -- 弹性，打卡时间如果超过弹性上班结束时间(8:00-9:00弹性上班,打卡时间>9:00)，此时不再享受弹性加班，按正常上班时间计算
              WHEN user_card_type = 21 AND shift_is_flextime = 1 THEN
--                CASE WHEN swipe_begin > (shift_begin + shift_flextime_interval_begin) THEN
--                  -- 按正常班次计算，打卡时间>排班下班时间，取排班下班时间，反之取打卡时间
--                  IIF(swipe_end > shift_end, shift_end, swipe_end)
--                ELSE 
--                  swipe_end
--                END
                swipe_end
              -- 打卡时间在第一次就餐开始时间和结束时间之间，以就餐开始时间计算
              WHEN swipe_end > repast_start_time AND swipe_end < repast_end_time THEN
                repast_start_time
              -- 打卡时间在第二次就餐开始时间和结束时间之间，以就餐开始时间计算
              WHEN swipe_end > repast_start_time2 AND swipe_end < repast_end_time2 THEN
                repast_start_time2
              -- 打卡时间>排班下班时间-下班前X分钟内不计迟到,<=签退结束时间
              -- 正常打卡，实际下班时间=排班下班时间，否则=下班打卡时间
              WHEN swipe_end > shift_end - shift_duty_off_delay AND swipe_begin <= shift_sign_end THEN
                shift_end
              ELSE
                swipe_end
            END
          ELSE
            swipe_begin_actual
        END

  --==================================================计算就餐时长==================================================

  --==================================================排班就餐时长==================================================

  UPDATE #timecard
  SET repast_is_83_normal =
    CASE
      WHEN shift_begin < repast_start_time AND shift_end > repast_end_time THEN
        1
      ELSE
        0
    END
  UPDATE #timecard
  SET repast_is_83_normal2 =
    CASE
      WHEN shift_begin < repast_start_time2 AND shift_end > repast_end_time2 THEN
        1
      ELSE
        0
    END

  -- 第一次就餐时间
  --  UPDATE #timecard
  --  SET repast_hours =
  --    CASE
  --      WHEN ISNULL(shift_id, 0) > 0 AND
  --        ISNULL(repast_start_time, 0) > 0 AND
  --        ISNULL(repast_end_time, 0) > 0 THEN
  --        CASE
  --          WHEN swipe_begin <= repast_start_time AND
  --            swipe_end >= repast_end_time THEN
  --            repast_end_time - repast_start_time
  --          ELSE
  --            0
  --        END
  --      ELSE
  --        0
  --    END

  -- 第一次就餐是否使用
  UPDATE #timecard
  SET repast_first =
    CASE
      WHEN ISNULL(shift_id, 0) > 0 AND
        ISNULL(repast_start_time, 0) > 0 AND
        ISNULL(repast_end_time, 0) > 0 THEN
        CASE
          WHEN swipe_begin <= repast_start_time AND
            swipe_end >= repast_end_time THEN
            1
          ELSE
            0
        END
      ELSE
        0
    END


  -- 第二次就餐时间
  --  UPDATE #timecard
  --  SET repast_hours =
  --    CASE
  --      WHEN ISNULL(shift_id, 0) > 0 AND
  --        ISNULL(repast_start_time2, 0) > 0 AND
  --        ISNULL(repast_end_time2, 0) > 0 THEN
  --        CASE
  --          WHEN swipe_begin <= repast_start_time2 AND
  --            swipe_end >= repast_end_time2 THEN
  --            ISNULL(repast_hours, 0) + (repast_end_time2 - repast_start_time2)
  --          ELSE
  --            repast_hours
  --        END
  --      ELSE
  --        repast_hours
  --    END

  -- 第二次就餐是否使用
  UPDATE #timecard
  SET repast_second =
    CASE
      WHEN ISNULL(shift_id, 0) > 0 AND
        ISNULL(repast_start_time2, 0) > 0 AND
        ISNULL(repast_end_time2, 0) > 0 THEN
        CASE
          WHEN swipe_begin <= repast_start_time2 AND
            swipe_end >= repast_end_time2 THEN
            1
          ELSE
            0
        END
      ELSE
        0
    END

  -- 非8+3班次就餐时长
  -- repast_first=1表示已经第一次就餐已经使用，repast_second=1表示第二次就餐已经使用，累加两次就餐时间
  UPDATE #timecard
  SET repast_hours = repast_hours + IIF(repast_first = 1, repast_end_time - repast_start_time, 0)
  WHERE ot_type_83 = 0
  UPDATE #timecard
  SET repast_hours = repast_hours + IIF(repast_second = 1, repast_end_time2 - repast_start_time2, 0)
  WHERE ot_type_83 = 0


  -- 8+3班次就餐时长
  UPDATE #timecard
  SET repast_hours = IIF(repast_first = 1 AND repast_is_83_normal = 1, repast_end_time - repast_start_time, 0)
  WHERE ot_type_83 > 0
  UPDATE #timecard
  SET repast_hours = repast_hours + IIF(repast_second = 1 AND repast_is_83_normal2 = 1, repast_end_time2 - repast_start_time2, 0)
  WHERE ot_type_83 > 0


  --==================================================加班就餐时长==================================================

  -- 8+3班次的加班就餐时间
  UPDATE #timecard
  SET ot_repast_hours = repast_end_time - repast_start_time
  WHERE ot_type_83 > 0 AND repast_is_83_normal = 0

  UPDATE #timecard
  SET ot_repast_hours = ot_repast_hours + (repast_end_time2 - repast_start_time2)
  WHERE ot_type_83 > 0 AND repast_is_83_normal2 = 0

  -- 加班申请的加班就餐时间
  UPDATE #timecard
  SET ot_repast_hours =
    CASE
      WHEN ot_is_eating = 1 THEN
        ot_repast_hours + 30
      ELSE
        ot_repast_hours
    END
  WHERE ot_type_id > 0

  --==================================================实出时长==================================================
  --SELECT swipe_end_actual,swipe_begin_actual,shift_begin,swipe_begin_actual,repast_hours,actual_hours FROM #timecard 


  -- 弹性，少于15分钟，计算为15分钟
  UPDATE #timecard
  SET swipe_begin_actual = IIF(swipe_begin_actual % 15 = 0, swipe_begin_actual, (swipe_begin_actual / 15 * 15 + 15))
  WHERE shift_is_flextime = 1 AND user_card_type = 21

  UPDATE #timecard
  SET actual_hours =
        CASE
          WHEN ISNULL(swipe_begin, 0) = 0 OR
            ISNULL(swipe_end, 0) = 0 THEN
            0
          -- 弹性
          WHEN user_card_type = 21 AND shift_is_flextime = 1 THEN
            --IIF(swipe_end > shift_end + shift_flextime_interval_end, shift_end + shift_flextime_interval_end, swipe_end) - swipe_begin_actual - repast_hours
            CASE
              WHEN swipe_begin > shift_begin + shift_flextime_interval_begin THEN
                shift_end - swipe_begin_actual - repast_hours
              ELSE
                swipe_end_actual - swipe_begin_actual - repast_hours
            END
          ELSE
            swipe_end_actual - IIF(swipe_begin_actual < shift_begin, shift_begin, swipe_begin_actual) - repast_hours
        END
     ,should_hours =
        CASE
          WHEN ISNULL(shift_id, 0) > 0 THEN
            shift_workday_hours
          ELSE
            0
        END
  --SELECT swipe_end_actual,swipe_begin_actual,shift_begin,swipe_begin_actual,repast_hours,actual_hours FROM #timecard




  UPDATE #timecard
  SET actual_hours =
    CASE
      WHEN ISNULL(actual_hours, 0) >= ISNULL(should_hours, 0) THEN
        should_hours
      ELSE
        actual_hours
    END

  -- 弹性，并且是弹性班次，下班时间是下班打卡时间
  --  UPDATE #timecard SET shift_actual_end = swipe_begin + should_hours + repast_hours WHERE user_card_type = 21 AND shift_is_flextime = 1



  --待处理时间 pending_hours 待处理时间字段=实际下班打卡时间-排班下班时间
  UPDATE #timecard
  SET pending_hours =
    CASE
      WHEN ISNULL(shift_id, 0) > 0 AND ISNULL(swipe_begin, 0) > 0 AND ISNULL(swipe_end, 0) > 0 THEN
        ISNULL(swipe_end, 0) - IIF(ot_type_83 = 1, ot_end, shift_actual_end)
      ELSE
        0
    END

  UPDATE #timecard
  SET pending_hours =
    CASE
      -- 弹性
      WHEN shift_is_flextime = 1 AND user_card_type = 21 THEN
        CASE
          WHEN swipe_begin > shift_begin + shift_flextime_interval_begin THEN
            -- 正常计算，取排班下班时间
            swipe_end - shift_end
          ELSE
            -- 弹性计算，取下班打卡时间
            swipe_end - (swipe_begin + should_hours + repast_hours)
        END
      ELSE
        pending_hours
    END

  -- ot_min_increment增量显示
  UPDATE #timecard
  SET pending_hours =
    CASE
      WHEN pending_hours > ot_min AND ot_type_83 != 1 THEN
        ot_min + ((pending_hours - ot_min) / ot_min_increment) * ot_min_increment
      --      --TODO 要验证
      --      WHEN pending_hours > ot_min AND ot_type_83 = 1 THEN
      --        pending_hours / ot_min_increment * ot_min_increment
      ELSE
        pending_hours
    END

  -- 8+3, 超出3的待处理时间以30分钟增量计算
  UPDATE #timecard
  SET pending_hours =
    CASE
      WHEN ot_type_83 = 1 THEN
        pending_hours / ot_min_increment * ot_min_increment
      ELSE
        pending_hours
    END

  -- 3+8，大于15分钟，显示15分钟，第二次是30分钟，往后30分钟增量 
  UPDATE #timecard
  SET pending_hours =
    CASE
      WHEN pending_hours > 15 AND pending_hours < 30 THEN
        15
      WHEN pending_hours >= 30 THEN
        pending_hours / ot_min_increment * ot_min_increment
      ELSE
        0
    END
  WHERE ot_type_83 = 2


  -- 待处理时间大于ot_min小时显示，小于ot_min小时的不显示 
  -- 8+3,3+8班次的待处理时间从加班结束时间开始计算，30分钟起计算
  UPDATE #timecard
  SET pending_hours = 0
  WHERE ISNULL(pending_hours, 0) < ot_min AND ot_type_83 = 0


  UPDATE #timecard
  SET pending_hours = 0
  WHERE ISNULL(pending_hours, 0) < 0





  --==================================================考勤状态 开始==================================================
  --正常打卡            0
  --处理后正常          2,3,15
  --处理正常(请假)     2
  --处理正常(补出勤)   3
  --迟到    4
  --早退    5
  --缺勤    6
  --加班异常  7
  --处理正常(请假) + 处理正常(补出勤)    15


  UPDATE #timecard
  SET status =
    CASE
      --排班
      WHEN ISNULL(shift_id, 0) > 0 THEN
        CASE
          --部门总监
          WHEN ISNULL(user_card_type, 0) = 22 THEN
            CASE
              WHEN ISNULL(total_hours, 0) >= ISNULL(should_hours, 0) THEN
                status
              ELSE
                6
            END
          --迟到=大于上班时间，小于迟到结束时间,弹性班次不计迟到
          WHEN swipe_begin <= shift_late AND shift_is_flextime = 0
            AND swipe_begin > (shift_begin + shift_duty_on_delay) THEN
            4
          -- 上班打卡时间大于迟到结束时间，算异常
          WHEN swipe_begin > shift_late THEN
            6
          --早退，弹性班次不计早退
          WHEN swipe_end >= shift_early AND shift_is_flextime = 0 AND
            swipe_end <= (shift_end - shift_duty_off_delay) THEN
            5
          --打卡异常
          WHEN ISNULL(swipe_begin, 0) = 0 OR
            ISNULL(swipe_end, 0) = 0 THEN
            6
          WHEN ISNULL(actual_hours, 0) + ISNULL(leave_hours_total, 0) < ISNULL(should_hours, 0) THEN
            6
          --实出时长<应出时长
          WHEN ISNULL(actual_hours, 0) < ISNULL(should_hours, 0) THEN
            6
          ELSE
            0
        END
      --未排班 
      --      WHEN ISNULL(shift_id, 0) = 0 THEN
      --        CASE
      --          WHEN ISNULL(ot_bill_id, 0) = 0 THEN
      --            CASE  --未加班
      --              WHEN
      --                --未加班，有打卡记录
      --                (ISNULL(swipe_begin, 0) > 0 OR ISNULL(swipe_end, 0) > 0) THEN
      --                6
      --              WHEN ISNULL(leave_hours_total, 0) > 0 THEN
      --                6
      --              ELSE
      --                status
      --            END
      --          ELSE
      --            status
      --        END
      ELSE
        status
    END


  UPDATE #timecard
  SET is_swipe =
    CASE
      WHEN ISNULL(swipe_first, 0) = 0 AND ISNULL(swipe_last, 0) = 0 THEN
        0
      ELSE
        1
    END

  -- 有排班，没有打卡记录
  UPDATE #timecard
  SET status =
    CASE
      WHEN (ISNULL(shift_id, 0) > 0) AND is_swipe = 0 THEN
        6
      ELSE
        status
    END


  -- 考勤异常，判断正常工时+请假时长，如果大于应出工时正常，反之异常
  UPDATE #timecard
  SET status =
    CASE
      WHEN ISNULL(status, 0) IN (5, 6) AND ISNULL(actual_hours, 0) + ISNULL(leave_hours_total, 0) >= ISNULL(should_hours, 0) THEN
        0
      ELSE
        status
    END

  -- 没排班，没加班，有打卡记录，异常
  UPDATE #timecard
  SET status = 6
  WHERE ISNULL(shift_id, 0) = 0 AND ISNULL(ot_bill_id, 0) = 0 AND (ISNULL(swipe_begin, 0) > 0 OR ISNULL(swipe_end, 0) > 0)


  --==================================================总工时 开始==================================================



  -- 加班已确认总工时
  UPDATE #timecard
  SET ot_pending_hours_confirm = ISNULL(ot_normal_hours, 0) + ISNULL(ot_weekend_hours, 0) + ISNULL(ot_holiday_hours, 0) + ISNULL(ot_to_rest_hours, 0)

  -- 正常工时（不包括请假）>= 应出工时，不计算请假时长
  UPDATE #timecard
  SET leave_hours_total = 0
     ,leave_hours = ''
  WHERE actual_hours >= should_hours

  --正常工时  
  UPDATE #timecard
  SET total_hours = ISNULL(actual_hours, 0) + ISNULL(leave_hours_total, 0)

  --有排班， 正常工时>=应出工时，考勤标记正常
  UPDATE #timecard
  SET status =
    CASE
      WHEN ISNULL(shift_id, 0) > 0 AND ISNULL(status, 0) IN (0, 2, 3, 15) AND ISNULL(total_hours, 0) > 0 AND ISNULL(total_hours, 0) >= ISNULL(should_hours, 0) THEN
        0
      ELSE
        status
    END

  -- 考勤异常时不计算总工时
  UPDATE #timecard
  SET total_hours =
    CASE
      WHEN ISNULL(status, 0) IN (0, 2, 3, 15) THEN
        total_hours
      ELSE
        0
    END


  -- 总工时=正常工时+加班工时+请假时长
  UPDATE #timecard
  SET total_hours = total_hours + ot_pending_hours_confirm


  --==================================================总工时 结束==================================================


  --==================================================加班待确认时长 开始==================================================

  --SELECT swipe_end,ot_end,ot_begin,ot_repast_hours,ot_bill_id,ot_pending_hours,ot_card_end,ot_card_begin FROM #timecard t
  --SELECT ot_card_end,ot_end,ot_card_begin FROM #timecard t

  UPDATE #timecard
  SET ot_pending_hours =
    CASE
--      WHEN ISNULL(status, 0) IN (-1, 0, 2, 3, 15) THEN
      WHEN ISNULL(status, 0) >= 0 THEN
        CASE
          -- 8+3/3+8加班+加班申请
          WHEN ot_type_83 IN (1, 2) AND ot_bill_id > 0 THEN
            ISNULL(IIF(swipe_end < ot_end, swipe_end, ot_end), 0) - ISNULL(ot_begin, 0)
            -- 判断加班时间是否包含吃饭时间，不包含不扣吃饭时间
            - IIF(repast_second = 0, 0, ot_repast_hours)
            -- 打卡时间<加班结束时间，不计算加班申请的时间
            + (IIF(swipe_end < ot_end, 0, ISNULL(ot_card_end, 0) - ISNULL(ot_card_begin, 0)))
          -- 8+3加班
          WHEN ot_type_83 = 1 THEN
            -- 打卡时间小于加班结束时间取打卡时间，反之取加班结束时间
            -- 加班时间如果包含吃饭时间，扣除吃饭时间30分钟，反之不扣除
            ISNULL(IIF(swipe_end < ot_end, swipe_end, ot_end), 0) - ISNULL(ot_begin, 0) - IIF(repast_second = 0, 0, ot_repast_hours)
          WHEN ot_type_83 = 2 THEN
            -- 3+8加班，加班待确认时间要显示
            IIF(swipe_end > ot_end, ot_end, swipe_end) - IIF(swipe_begin < ot_begin, ot_begin, swipe_begin)
          -- 加班申请
          WHEN ISNULL(ot_bill_id, 0) > 0 THEN
            IIF(ot_card_end >= ot_end, ot_end, ot_card_end) - ISNULL(ot_card_begin, 0)
          ELSE
            0
        END
      ELSE
        0
    END

--  -- 加班申请
--  UPDATE #timecard
--  SET ot_pending_hours = IIF(ot_card_end >= ot_end, ot_end, ot_card_end) - ISNULL(ot_card_begin, 0)
--  WHERE ISNULL(ot_bill_id, 0) > 0 AND ISNULL(status, 0) IN (-1, 0, 2, 3, 15) AND ot_type_83 = 0

  --SELECT swipe_end,ot_end,ot_begin,ot_repast_hours,ot_bill_id,ot_pending_hours,ot_card_end,ot_card_begin,repast_is_83_normal2,t.repast_second FROM #timecard t
  --SELECT ot_card_end,ot_end,ot_card_begin,ot_pending_hours FROM #timecard t

  -- ot_min_increment增量显示
  UPDATE #timecard
  SET ot_pending_hours =
    CASE
      WHEN ot_pending_hours > 0 AND ot_pending_hours >= ot_min THEN
        ot_min + ((ot_pending_hours - ot_min) / ot_min_increment) * ot_min_increment
      ELSE
        ot_pending_hours
    END


  -- 加班申请时长，扣除吃饭时间
  UPDATE #timecard
  SET ot_hours =
    CASE
      WHEN ot_hours > 0 THEN
        ot_hours - ot_repast_hours
      ELSE
        ot_hours
    END
  WHERE ot_bill_id > 0 OR ot_bill_id = -1

  -- 如果有排班，有加班申请，待处理时间=待处理时间-加班待确认时间
  UPDATE #timecard
  SET pending_hours = pending_hours - ot_pending_hours
  WHERE ot_bill_id > 0 AND shift_id > 0 AND ot_type_83 != 1

  UPDATE #timecard
  SET pending_hours = 0
  WHERE ot_bill_id > 0 AND shift_id > 0 AND pending_hours < 0


  --如果加班待确认时长小于0就更新为0
  UPDATE #timecard
  SET ot_pending_hours =
    CASE
      WHEN ISNULL(ot_pending_hours, 0) < 0 THEN
        0
      WHEN ISNULL(ot_pending_hours, 0) > ISNULL(ot_hours, 0) THEN
        ot_hours
      ELSE
        ot_pending_hours
    END

  -- 最小加班时长
  UPDATE #timecard
  SET ot_pending_hours =
    CASE
      WHEN (ot_bill_id > 0 OR ot_bill_id = -1) AND ISNULL(ot_pending_hours, 0) < ISNULL(ot_min, 0) THEN
        0
      ELSE
        ot_pending_hours
    END

  -- 考勤异常，不能操作加班确认
  UPDATE #timecard
  SET ot_bill_status = 0
  WHERE status IN(4, 5, 6)
    
  -- 加班待确认工是=0，不能操作加班确认
  UPDATE #timecard
  SET ot_bill_status = 0
  WHERE ISNULL(ot_pending_hours, 0) = 0
  --==================================================加班待确认时长 结束==================================================



  UPDATE #timecard
  SET status =
    CASE
      -- 处理正常（补出勤）  3
      WHEN flag = 1 THEN
        CASE
          WHEN status = 0 THEN
            3
          WHEN status = 2 THEN
            15
          ELSE
            status
        END
      -- 处理正常（请假）   2
      WHEN flag = 2 THEN
        CASE
          WHEN status = 0 THEN
            2
          WHEN status = 3 THEN
            15
          ELSE
            status
        END
      ELSE
        status
    END

  --==================================================考勤状态 结束==================================================


  -- 补出勤有效打卡显示蓝框
  UPDATE #timecard
  SET swipe_begin_repair = 1
  FROM #timecard tc
  INNER JOIN #cardData cd ON cd.user_serial = tc.user_serial AND cd.mode = 1 AND tc.swipe_begin = cd.swipe_time
  WHERE ISNULL(tc.swipe_begin, 0) > 0 AND tc.status IN (2, 3, 15)

  UPDATE #timecard
  SET swipe_end_repair = 1
  FROM #timecard tc
  INNER JOIN #cardData cd ON cd.user_serial = tc.user_serial AND cd.mode = 1 AND tc.swipe_end = cd.swipe_time
  WHERE ISNULL(tc.swipe_end, 0) > 0 AND tc.status IN (2, 3, 15)


--  SELECT * FROM #timecard WHERE user_serial = 20103846


  BEGIN TRY
    BEGIN TRANSACTION

    -- 更新考勤卡表数据
    MERGE
    INTO bd_kq_timecard p
    USING #timecard s ON p.user_serial = s.user_serial
      AND p.card_date = s.card_date
    WHEN MATCHED
      THEN UPDATE SET
          p.user_no = s.user_no
          , p.user_name = s.user_name
          , p.dept_id = s.dept_id
          , p.dept_name = s.dept_name
          , p.card_week = s.card_week
          , p.shift_id = s.shift_id
          , p.shift_name = s.shift_name
          , p.shift_begin = s.shift_begin
          , p.shift_end = s.shift_end
          , p.swipe_begin = s.swipe_begin
          , p.swipe_end = s.swipe_end
          , p.should_hours = s.should_hours
          , p.actual_hours = s.actual_hours
          , p.total_hours = s.total_hours
          , p.status = s.status
          , p.shift_middle_count = s.shift_middle_count
          , p.shift_night_count = s.shift_night_count
          , p.pending_hours = s.pending_hours
          , p.ot_type_id = s.ot_type_id
          , p.ot_type_name = s.ot_type_name
          , p.ot_begin = s.ot_begin
          , p.ot_end = s.ot_end
          , p.ot_hours = s.ot_hours
          , p.ot_is_eating = s.ot_is_eating
          , p.ot_pending_hours = s.ot_pending_hours
          , p.leave_type = s.leave_type
          , p.leave_hours = s.leave_hours
          , p.ot_bill_id = s.ot_bill_id
          --            , p.leave_hours_total = s.leave_hours_total
          --            , p.ot_normal_hours = s.ot_normal_hours
          --            , p.ot_weekend_hours = s.ot_weekend_hours
          --            , p.ot_holiday_hours = s.ot_holiday_hours
          --            , p.ot_to_rest_hours = s.ot_to_rest_hours
          , p.sign_begin = s.sign_begin_date
          , p.sign_end = s.sign_end_date
          , p.is_swipe = s.is_swipe
          , p.leave_bill_id = s.leave_bill_id
          , p.ot_bill_status = s.ot_bill_status
          , p.swipe_begin_repair = s.swipe_begin_repair
          , p.swipe_end_repair = s.swipe_end_repair
          , p.op_flag =
            CASE
              WHEN @flag <> 0 THEN
                @flag
              ELSE
                p.op_flag
            END
    WHEN NOT MATCHED BY TARGET
      THEN INSERT(user_serial, user_no, user_name, dept_id, dept_name, card_date, card_week, shift_id, shift_name, shift_begin, shift_end, swipe_begin, swipe_end, should_hours, actual_hours, total_hours, status, shift_middle_count, shift_night_count, pending_hours, ot_bill_id, ot_type_id, ot_type_name, ot_begin, ot_end, ot_hours, ot_is_eating, ot_pending_hours, leave_type, leave_hours, leave_hours_total, sign_begin, sign_end, is_swipe, leave_bill_id, ot_bill_status, swipe_begin_repair, swipe_end_repair, op_flag)
          VALUES(user_serial, user_no, user_name, dept_id, dept_name, card_date, card_week, shift_id, shift_name, shift_begin, shift_end, swipe_begin, swipe_end, should_hours, actual_hours, total_hours, status, shift_middle_count, shift_night_count, pending_hours, ot_bill_id, ot_type_id, ot_type_name, ot_begin, ot_end, ot_hours, ot_is_eating, ot_pending_hours, leave_type, leave_hours, ISNULL(leave_hours_total, 0), sign_begin_date, sign_end_date, is_swipe, leave_bill_id, ot_bill_status, swipe_begin_repair, swipe_end_repair, @flag)
    ;

    -- 更新考勤打卡记录表数据
    UPDATE kj
    SET whether_validity = cs.is_valid--, valid_date = CASE WHEN cs.is_valid IN (1, 3) THEN @date ELSE valid_date END
    FROM #cardSave cs
    INNER JOIN kt_jl kj ON cs.bh = kj.bh --AND kj.whether_validity < 2

    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    PRINT
    'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
    ', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
    ', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) +
    ', Line ' + CONVERT(VARCHAR(5), ERROR_LINE())

    PRINT ERROR_MESSAGE();

    IF XACT_STATE() <> 0
    BEGIN
      ROLLBACK TRANSACTION
    END

  END CATCH;



  DROP TABLE #timecard
  DROP TABLE #cardData
  DROP TABLE #cardSave

  SET NOCOUNT OFF;
END